<%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<%@ page language="java" import="net.sf.json.JSONArray" %>
<%@ page language="java" import="net.sf.json.JSONObject" %> 
<%!
public String array2String(String[] arrayS, String separator) 
{
	String result = "\'";
    if (arrayS.length > 0) {
        for (int i=0; i<arrayS.length - 1; i++) {
            result = result + "[[:<:]]" + arrayS[i] + "[[:>:]]" + separator;
        }
	result = result + "[[:<:]]" + arrayS[arrayS.length - 1] + "[[:>:]]" + "\'";
    }
    return result;	
}
%>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm"; 

Connection connection = null; 

Class.forName("com.mysql.jdbc.Driver").newInstance(); 

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()){
String newString = null;
Statement statement = null;
Statement stm2 = null;
ResultSet rs = null; 
String expression = null;
ResultSet rs2 = null;
String title = null;
String edition = null;
String courses = null;
String authors = null;
String publisher = null;
String isbn = null;
String count = null;
String sep = " ";
String copyID = null;
String copyCondition = null;
String copyPrice = null;
String copyAvail = null;
JSONArray arrayObj=new JSONArray();
JSONArray copyLikeNew = new JSONArray();
JSONArray copyGood = new JSONArray();
JSONArray copyFair = new JSONArray();

newString = request.getParameter("input");
String[] newStringArray = newString.split(sep);

String L = array2String(newStringArray, "|");
expression = "SELECT * FROM Books where Books.BookTitle REGEXP " + L + " or Books.Courses REGEXP " + L + " or Books.Authors REGEXP " + L + " or Books.Publisher REGEXP " + L + "or Books.ISBN REGEXP " + L;

statement = connection.createStatement();
rs = statement.executeQuery(expression);

while (rs.next()) {
title = rs.getString("Books.BookTitle");
edition = rs.getString("Books.Edition");
courses = rs.getString("Books.Courses");
authors = rs.getString("Books.Authors");
publisher = rs.getString("Books.Publisher");
isbn = rs.getString("Books.ISBN");

stm2 = connection.createStatement();
rs2 = stm2.executeQuery("SELECT * FROM BookCopies where BookCopies.ISBN = '" + isbn + "' AND Availability <> 'N/A'");

int count_copies_available = 0;
while(rs2.next()){
count_copies_available++;

}
count = String.valueOf(count_copies_available);

JSONObject first =  new JSONObject();
first.put("Title", title);
first.put("Edition", edition);
first.put("Courses", courses);
first.put("Authors", authors);
first.put("Publisher", publisher);
first.put("ISBN", isbn);
first.put("Copies", count);

arrayObj.add(first);

} 

out.print(arrayObj);

}

connection.close();

}
catch(Exception ex){
out.print(ex);

} 
%>